﻿
using Microsoft.International.Converters.TraditionalChineseToSimplifiedConverter;
using System;
using System.Text.RegularExpressions;
using zyconfig;

namespace zyui
{
    public class SqlText
    {
        public static string GetLanguage(string LanguageData)
        {
            string str = LanguageData;
            return str.ToUpper().Replace("\x00c2", "A").Replace("\x00c0", "A").Replace("\x00c7", "C").Replace("\x00ca", "E").Replace("\x00cb", "E").Replace("\x00c9", "E").Replace("\x00c8", "E").Replace("\x00ce", "I").Replace("\x00cf", "I").Replace("\x00d4", "0").Replace("\x00db", "U").Replace("\x00dc", "U").Replace("\x00d9", "U").Replace("\x00c4", "A").Replace("\x00cb", "E").Replace("\x00d6", "O").Replace("\x00dc", "U").Replace("\x00c0", "A").Replace("\x00c8", "E").Replace("\x00d2", "O");
        }
        public static string BuindPosSql(string strSql)
        {
            string str = strSql;
            return str.Replace("select", "").Replace("having", "").Replace("group", "").Replace("from", "").Replace("where", "").Replace("update", "").Replace("insert", "").Replace("create", "").Replace("exec", "").Replace("execute", "").Replace("@", "").Replace("%", "").Replace(";", "").Replace("'", "").Replace("\"", "").Replace("!", "").Replace("--", "").Replace("1=1", "").Replace("'", "");
        }
        public static string BarCodeToISBN(string barCode)
        {
            barCode = barCode.Replace("-", "");
            if (barCode.Length != 13)
            {
                return barCode;
            }
            if (!Regex.IsMatch(barCode, @"\d{13}"))
            {
                return barCode;
            }
            string str = barCode.Substring(3, 9);
            int num = 0;
            for (int i = 10; i >= 2; i--)
            {
                num += i * Convert.ToInt32(str.Substring(10 - i, 1));
            }
            string str2 = null;
            if ((num % 11) == 0)
            {
                str2 = "0";
            }
            else if ((num % 11) == 1)
            {
                str2 = "X";
            }
            else
            {
                str2 = (11 - (num % 11)).ToString();
            }
            return (str + str2);
        }
        public static string BuingISBNSept(string strISBNCode)
        {
            string str;
            if (strISBNCode.Length != 10)
            {
                return strISBNCode;
            }
            string str2 = strISBNCode.Substring(1, 1);
            if (str2 == "0")
            {
                str = strISBNCode.Substring(0, 1) + "-" + strISBNCode.Substring(1, 2) + "-" + strISBNCode.Substring(3, 6) + "-" + strISBNCode.Substring(9, 1);
            }
            else
            {
                if ((Convert.ToInt32(str2) >= 1) && (Convert.ToInt32(str2) <= 4))
                {
                    return (strISBNCode.Substring(0, 1) + "-" + strISBNCode.Substring(1, 3) + "-" + strISBNCode.Substring(4, 5) + "-" + strISBNCode.Substring(9, 1));
                }
                if ((Convert.ToInt32(str2) >= 5) && (Convert.ToInt32(str2) <= 7))
                {
                    return (strISBNCode.Substring(0, 1) + "-" + strISBNCode.Substring(1, 4) + "-" + strISBNCode.Substring(5, 4) + "-" + strISBNCode.Substring(9, 1));
                }
                if (str2 == "8")
                {
                    str = strISBNCode.Substring(0, 1) + "-" + strISBNCode.Substring(1, 5) + "-" + strISBNCode.Substring(6, 3) + "-" + strISBNCode.Substring(9, 1);
                }
                else
                {
                    str = strISBNCode.Substring(0, 1) + "-" + strISBNCode.Substring(1, 6) + "-" + strISBNCode.Substring(7, 2) + "-" + strISBNCode.Substring(9, 1);
                }
            }
            return str;
        }
        public static string Decompose(string strIn)
        {
            string str = string.Empty;
            try
            {
                for (int i = 0; i <= (strIn.Length - 1); i++)
                {
                    char ch = strIn[i];
                    str = str + @"\" + EncodeGb(ch.ToString());
                }
            }
            catch (Exception)
            {
                str = " ";
            }
            return str;
        }
        public static string EncodeGb(string strIn)
        {
            string str = string.Empty;
            try
            {
                for (int i = 0; i <= (strIn.Length - 1); i++)
                {
                    string str2 = Convert.ToInt32(strIn[i]).ToString("X4");
                    str = str + str2;
                }
            }
            catch (Exception)
            {
                str = " ";
            }
            return str;
        }
        public static string GetXnkName(int strHouseName)
        {
            string str = "";
            using (DbOperHandler _open = new Common().Doh())
            {
                _open.Reset();
                _open.ConditionExpress = "虚拟库室=" + strHouseName;
                str = _open.GetField("虚拟库室", "库室名称", true).ToString();
            }
            return str;
        }
        public static string GetHouseName(string strHouseName)
        {
            string str = "";
            using (DbOperHandler _open = new Common().Doh())
            {
                _open.Reset();
                _open.ConditionExpress = "馆藏地址='" + strHouseName + "'";
                str = _open.GetField("馆藏地址定义", "部门名称", true).ToString();
            }
            return str;
        }
        private static string GetHouseCode(string strHouseName)
        {
            string str;
            using (DbOperHandler _open = new Common().Doh())
            {
                _open.Reset();
                _open.ConditionExpress = "部门名称='" + strHouseName + "'";
                str = _open.GetField("馆藏地址定义", "馆藏地址", true).ToString();
            }
            return str;
        }

        public static string DigitalizeSql(string TableName, string FildName, string Kay, string Type, int ScarchType, string DBType, bool all, bool fsc)
        {
            string str = FildName;
            string barCode = Kay;
            if (FildName.Equals("索取号"))
            {
                barCode = barCode.ToUpper();
            }
            if (FildName.Equals("标准编码") && TableName.Equals("情报检索一对多库"))
            {
                barCode = barCode.Replace("-", "");
                if (barCode.Length == 13)
                {
                    barCode = BarCodeToISBN(barCode);
                }
                barCode = BuingISBNSept(barCode);
            }

            barCode = new System.Text.RegularExpressions.Regex("[\\s]+").Replace(barCode, " ");
            barCode = barCode.Trim();//
            string[] sArray = barCode.Split(',');
            if (!TableName.Equals("情报检索库"))
            {
                str = "类型=" + ScarchType + " AND 数据";
            }
            string str3 = " ";
            if (!string.IsNullOrEmpty(barCode) || all)
            {
                if (sArray.Length == 1)
                {
                    if (Type.Equals("前方一致"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.SimplifiedToTraditional);
                            str3 = str + "  LIKE UNISTR('" + Decompose(barCode) + @"\0025')  ";
                        }
                        else
                        {
                            str3 = str + "  LIKE '" + barCode + "%'  ";
                        }
                    }
                    else if (Type.Equals("中间一致"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.TraditionalToSimplified);
                            str3 = str + @" Like UNISTR('\0025" + Decompose(barCode) + @"\0025')  ";
                        }
                        else
                        {
                            str3 = str + " Like '%" + barCode + "%'";
                        }
                    }
                    else if (Type.Equals("后方一致"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.SimplifiedToTraditional);
                            str3 = str + @" Like  UNISTR('\0025" + Decompose(barCode) + "')  ";
                        }
                        else
                        {
                            str3 = str + " Like  '%" + barCode + "'";
                        }
                    }
                    else if (Type.Equals("绝对一致"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.SimplifiedToTraditional);
                            str3 = str + @"=UNISTR('" + Decompose(barCode) + "')  ";
                        }
                        else
                        {
                            str3 = str + "='" + barCode + "'";
                        }
                    }
                    else if (Type.Equals("等于"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.SimplifiedToTraditional);
                            str3 = str + " = UNISTR('" + Decompose(barCode) + "')  ";
                        }
                        else
                        {
                            str3 = str + " = '" + barCode + "'  ";
                        }
                    }
                    else if (Type.Equals("大于"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.SimplifiedToTraditional);
                            str3 = str + " > UNISTR('" + Decompose(barCode) + "')  ";
                        }
                        else
                        {
                            str3 = str + " > '" + barCode + "'  ";
                        }
                    }
                    else if (Type.Equals("小于"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.SimplifiedToTraditional);
                            str3 = str + " < UNISTR('" + Decompose(barCode) + "')  ";
                        }
                        else
                        {
                            str3 = str + " < '" + barCode + "'  ";
                        }
                    }
                }
                else
                {
                    if (Type.Equals("介于") && sArray.Length == 2)
                    {
                        str3 += "(" + str + " BETWEEN  ";
                        for (int i = 0; i < sArray.Length; i++)
                        {
                            str3 += "'" + sArray[i] + "' AND ";
                        }
                        str3 = str3.Substring(0, str3.Length - 5);
                        str3 += ") ";
                    }
                }
            }
            else
            {
                str3 = "(" + str + " is null or " + str + "='')";
            }
            if (string.IsNullOrEmpty(str3.Trim()))
                str3 = " g.主键码 is not null ";
            if (!TableName.Equals("情报检索库"))
            {
                str3 = " EXISTS(SELECT 主键码 FROM " + TableName + " WHERE " + str3 + " and 主键码=g.主键码)";
            }
            if (fsc)
                return str3 + " AND EXISTS(SELECT 主键码 FROM DISK_情报检索文件信息表 where 主键码=g.主键码) ";
            else
                return str3;

        }
        public static string BuindSql(string TableName, string FildName, string Kay, string Type, int ScarchType, string DBType, bool all,bool fs)
        {
            string str = FildName;
            string barCode = Kay;
            if (FildName.Equals("索书号"))
            {
                barCode = barCode.ToUpper();
            }
            if (FildName.Equals("标准编码") && TableName.Equals("馆藏书目库"))
            {
                if (!barCode.Contains("-"))
                    str = " REPLACE(" + FildName + ",'-','')";
            }
            if (FildName.Equals("标准编码") && TableName.Equals("检索编码库"))
            {
                barCode = barCode.Replace("-", "");
                if (barCode.Length == 13)
                {
                    barCode = BarCodeToISBN(barCode);
                }
                barCode = BuingISBNSept(barCode);
            }
            if (FildName.Equals("馆藏地址"))
            {
                barCode = GetHouseCode(barCode);
            }

            barCode = new System.Text.RegularExpressions.Regex("[\\s]+").Replace(barCode, " ");
            barCode = barCode.Trim();//
            string[] sArray = barCode.Split(',');
            if (TableName.IndexOf("检索") >= 0)
            {
                str = "类型=" + ScarchType + " AND 数据";
                if (TableName == "检索索书号库")
                    str = " 索书号";
            }
            string str3 = " ";
            if (!string.IsNullOrEmpty(barCode) || all)
            {
                if (sArray.Length == 1)
                {
                    if (Type.Equals("前方一致"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.SimplifiedToTraditional);
                            str3 = str + "  LIKE UNISTR('" + Decompose(barCode) + @"\0025')  ";
                        }
                        else
                        {
                            str3 = str + "  LIKE '" + barCode + "%'  ";
                        }
                    }
                    else if (Type.Equals("中间一致"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.TraditionalToSimplified);
                            str3 = str + @" Like UNISTR('\0025" + Decompose(barCode) + @"\0025')  ";
                        }
                        else
                        {
                            str3 = str + " Like '%" + barCode + "%'";
                        }
                    }
                    else if (Type.Equals("后方一致"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.SimplifiedToTraditional);
                            str3 = str + @" Like  UNISTR('\0025" + Decompose(barCode) + "')  ";
                        }
                        else
                        {
                            str3 = str + " Like  '%" + barCode + "'";
                        }
                    }
                    else if (Type.Equals("绝对一致"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.SimplifiedToTraditional);
                            str3 = str + @"=UNISTR('" + Decompose(barCode) + "')  ";
                        }
                        else
                        {
                            str3 = str + "='" + barCode + "'";
                        }
                    }
                    else if (Type.Equals("等于"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.SimplifiedToTraditional);
                            str3 = str + " = UNISTR('" + Decompose(barCode) + "')  ";
                        }
                        else
                        {
                            str3 = str + " = '" + barCode + "'  ";
                        }
                    }
                    else if (Type.Equals("大于"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.SimplifiedToTraditional);
                            str3 = str + " > UNISTR('" + Decompose(barCode) + "')  ";
                        }
                        else
                        {
                            str3 = str + " > '" + barCode + "'  ";
                        }
                    }
                    else if (Type.Equals("小于"))
                    {
                        if (DBType.ToUpper().IndexOf("ORACLE") > 0)
                        {
                            barCode = ChineseConverter.Convert(barCode, ChineseConversionDirection.SimplifiedToTraditional);
                            str3 = str + " < UNISTR('" + Decompose(barCode) + "')  ";
                        }
                        else
                        {
                            str3 = str + " < '" + barCode + "'  ";
                        }
                    }
                }
                else
                {
                    if (Type.Equals("介于") && sArray.Length == 2)
                    {
                        str3 += "(" + str + " BETWEEN  ";
                        for (int i = 0; i < sArray.Length; i++)
                        {
                            str3 +=   "'"+sArray[i] + "' AND ";
                        }
                        str3 = str3.Substring(0, str3.Length - 5);
                        str3 += ") ";
                    }
                }
            }
            else
            {
                str3 = "(" + str + " is null or " + str + "='')";
            }
            if (string.IsNullOrEmpty(str3.Trim()))
                str3 = " g.主键码 is not null ";
            if (TableName.IndexOf("检索") >= 0)
            {
                if (TableName == "检索多对多库")
                    str3 = " EXISTS(SELECT 主键码 FROM 检索联接库 WHERE EXISTS(SELECT 子键码 FROM " + TableName + " WHERE " + str3 + " and 子键码=检索联接库.子键码) and 主键码=g.主键码)";
                else str3 = " EXISTS(SELECT 主键码 FROM " + TableName + " WHERE " + str3 + " and 主键码=g.主键码)";
            }
            else
            {
                if (TableName == "馆藏典藏库" || TableName == "采购库")
                    return str3 = " EXISTS(SELECT 主键码 FROM " + TableName + " WHERE " + str3 + " and 主键码=g.主键码) ";
            }
            if (fs)
                str3 += " and ( EXISTS(SELECT 主键码 from Disk_文件信息表 where 主键码=g.主键码 ) or EXISTS(SELECT 主键码 FROM 馆藏典藏库 WHERE EXISTS(SELECT 主键码 from Disk_复本文件信息表 where 登录号=馆藏典藏库.登录号 ) and 主键码=g.主键码 ))";
            return str3;

        }
    }
}
